Re: Question on locking - Mailing list pgsql-novice

I don't agree with Tom's approach because it can only work in a very
limited number of cases. Imagine 3 persons editing the same record at
about the same time.

The second one to update has to solve the conflict with the first.
In cases where this is done manually and not automatically, which
would be the safest thing to do in most cases I guess(1), you can
even imagine person 2 calling person 1 for clarifications etc.

In the mean time person 3 wants to update but is also confronted with
conflicts from person 1's update and starts working on solving them.
When he's done he gets another conflict again (if the program is well
made) because in the mean time person 2 has resolved his conflict and
committed his update.  Person 3 can start over with this newest
conflict.

At the end of the road there's been a lot of confusion, a huge waste
of man-hours and a disgruntled employer who has to pay the wages for
this.

And the risk if the program is not well made is that person 3 will
only notice the conflict with person 1 but not with person 2!

Locking other (human) users out of a record that is being updated is
the only fool-proof method I have ever been able to imagine.
PostgreSQL lacks in this aspect.  If it's not good to have lengthy
transactions for all sorts of reasons then PostgreSQL needs to come
up with some command 'LOCK TILL UPDATE' that works outside
transactions.

my 2 cents,

Marc

(1) imagine the one person has changed the prefix of a phone number
and the second one changed the extension in the same field.  An
automatic conflict solver can't cope with this.  A manual conflict
solver would need to show the original record, the changes made by
the one user, those made by the other, and if a third user comes in
his changes as well etc.  This would make for a very confusing and
cumbersome interface and huge complications for the developer.  The
only elegant way out is locking users out of records and have them do
their edits one by one, and not all at the same time.

>Greetings:
>
>I posted a question regarding this issue about 2 weeks ago. See "Question
>Regarding Locks" from 10/27/04. Tom Lane resonded with the following:
>
>"To me, this says that you're already off on the wrong foot.
>
>You don't ever want your client application holding locks while a
>human user edits text, drinks coffee, goes out to lunch, or whatever.
>A better design is to fetch the data without locking it, allow the
>user to edit as he sees fit, and then when he clicks "save" you do
>something like
>
>         begin;
>         select row for update;
>         if [ row has not changed since you originally pulled it ] then
>                 update row with changed values;
>                 commit;
>         else
>                 abort;
>                 notify user of conflicts
>                 let user edit new data to resolve conflicts and try again
>         fi
>
>In this design the row lock is only held for milliseconds.
>
>You need to provide some code to let the user merge what he did with the
>prior changes, so that he doesn't have to start over from scratch in the
>failure case.  What "merge" means requires some business-logic knowledge
>so I can't help you there, but this way you are spending your effort on
>something that actually helps the user, rather than just tells him he
>has to wait.  Performance will be much better too --- long-lasting
>transactions are nasty for all sorts of reasons.
>
>BTW, a handy proxy for "row has not changed" is to see if its XMIN
>system column is still the same as before.  If so, no transaction has
>committed an update to it.  (This may or may not help much, since you're
>probably going to end up groveling over all the fields anyway in the
>"notify user" part, but it's a cool hack if you can use it.)
>
>                         regards, tom lane"
>
>I have carefully considered his advice and I will be implementing his
>suggestions within a couple weeks.
>
>Thanks...
>
>On Friday 05 November 2004 02:36 am, Steve Tucknott saith:
>  > PostGreSQL 7.4.5
>>
>>  If I have the situation where process 1 has selected record1 from table
>>  a for update and then process 2 tries to do the same, am I right in
>>  assuming that process 2 will wait until the first process completes the
>>  transaction (I've looked at Chapter 12 and this is intimated).
>>  How can I detect the lock on process 2? I want to be able to tell the
>>  user that the row is tentatively locked and to allow them to abort the
>>  update attempt. I can't see a 'SET LOCK MODE TO NOT WAIT' style command,
>>  so how do I stop process 2 from waiting?
>>  Is the suggested route to interrogate the system tables prior to
>>  selecting for update, to see if a lock has been applied?
>>
>>  Normally we wait on locks , so this is not an issue.
>>
>>
>>  Regards,
>>
>>  Steve Tucknott
>>
>>  ReTSol Ltd
>>
>>  DDI: 01903 828769
>
>--
>
>  Work: 1-336-372-6812
>  Cell: 1-336-363-4719
>email: terry@esc1.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings